﻿using DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Data;
using DBUtility;

namespace DAL
{
    public class SEOutStockDAL
    {
        public DataSet GetSEOutStockList()
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();


           
            //commandText.Append("  select ROW_NUMBER()  over(order by c.FBillNo) Num, ");
            //commandText.Append("  c.FBillNo,c.Fdate,c.Fry,c.qty,isnull(c.qty-c.scanqty,0) leftqty,c.FSku,c.StickLen  ");
            //commandText.Append("  , case  c.PkgsIsRegular when 1 then '定尺' else '不定尺' end PkgsIsRegular,e.NAME,f.FItemName,f.AttrValue from (");
            //commandText.Append(" 	select FBillNo,CONVERT(varchar(100), Fdate, 23) Fdate, Fry,");
            //commandText.Append(" 	sum(case states when 1 then 1 else 0 end)  qty ,");
            //commandText.Append(" 	sum( case flag when 1 then 1 else 0 end)  scanqty ,FSku,StickLen,PkgsIsRegular  ");
            //commandText.Append(" 	from t_SEOutStockDetail group by FBillNo, CONVERT(varchar(100), Fdate, 23), Fry,FSku,StickLen,PkgsIsRegular) c ");
            //commandText.Append("   left join t_SEOutStock d on d.FBillNo = c.FBillNo left join sys_master e on c.Fry = e.ID   ");
            //commandText.Append("   left join t_ICItemSku f on f.FSku = c.FSku     where d.States ='2'");

            commandText.Append("  select c.FBillNo, convert( varchar(10),c.Fdate,120) Fdate,e.NAME,c.Fry,");
            commandText.Append("  	sum(case c.states when 1 then c.FQty else 0 end)   total, ");
            commandText.Append("  	sum(case c.states when 1 then 1 else 0 end) num,d.Fname from t_SEOutStockDetail c ");
            commandText.Append("  left join t_SEOutStock d   on d.FBillNo = c.FBillNo  ");
            commandText.Append("  left join sys_master e on c.Fry = e.ID    ");
            commandText.Append("  where d.States ='2'    group by c.FBillNo , convert( varchar(10),c.Fdate,120) , e.NAME,c.Fry,d.Fname ");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        /// <summary>
        /// 获取出库明细
        /// </summary>
        /// <param name="FBillNo"></param>
        /// <returns></returns>
        public DataSet GetSEOutStockDetail(string FBillNo, string PosiID)
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();

            commandText.Append("  select g.FBillNo FSourceBillNo, b.PkgsID,b.FInterID,b.PkgsBarCode,b.HelperCode,  a.PlaceBarCode,");
            commandText.Append("  (case when  b.sxnum%2=0 then '◆' when b.sxnum%2 <> 0 then '★' else '' end)+b.Fnumber +'层'+  CAST(h.Floor AS nvarchar) PlaceName ,b.Floor, b.Fsku,d.FItemName,d.AttrValue,  b.StickLen,b.Stick,b.FQty, ");
            commandText.Append("  case SUBSTRING(b.HelperCode,1,1)  when 'A' then '1'  when 'B' then  '2'     end PkgsIsRegular ,");
            commandText.Append("  b.DFInterID,b.DFEntryID,b.FDetailID,b.FBillNo,b.FEntryID,b.sxnum,g.FName     ");
            commandText.Append("  from t_SEOutStockDetail b ");
            commandText.Append("   left join t_StockPlace a on b.PosiID = a.FSPID    	");
            commandText.Append("   left join t_ICItemSku d on d.FSku = b.FSku           	");
            commandText.Append("   left join t_SEOrder g on g.FInterID = b.DFInterID  ");
            commandText.Append("   left join stk_Package h on h.PkgsBarCode=b.PkgsBarCode");
            commandText.Append("   where b.FBillNo ='" + FBillNo + "'   and b.states ='1'");
            if (PosiID != "")
            {
                commandText.Append(" and b.PosiID ='" + PosiID + "'");

            }
            commandText.Append("   and flag = 0  order by b.pxnum,b.sxnum desc,b.PosiID asc ,b.Floor desc ");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;

        }

        /// <summary>
        ///取得条码的信息
        /// </summary>
        /// <param name="PkgsBarCode"></param>
        /// <param name="PlaceBarCode"></param>
        /// <param name="PlaceName"></param>
        /// <param name="Fsku"></param>
        /// <param name="StickLen"></param>
        /// <param name="Stick"></param>
        /// <param name="FQty"></param>
        /// <param name="PkgsIsRegular"></param>
        /// <returns></returns>
        /// //PkgsIsRegular 1 倍长，2 不定尺 
        public bool GetSEOutStockCode(string PkgsBarCode, out string HelperCode, out string PlaceBarCode, out string PlaceName, out string Floor, out string Fsku, out string StickLen, out string Stick, out string FQty, out string PkgsIsRegular)
        {
            //string HelperCode; string PlaceBarCode; string PlaceName; string Floor; string Fsku; string StickLen; string Stick; string FQty; string PkgsIsRegular;
         
            //
            bool result = false;
            StringBuilder commandText = new StringBuilder();
            DataSet ds = new DataSet();
            PlaceBarCode = ""; Floor = "";
            PlaceName = ""; Fsku = "";
            StickLen = ""; Stick = "";
            FQty = ""; PkgsIsRegular = ""; HelperCode = "";
            commandText.Append(" select a.PkgsBarCode,a.HelperCode,b.PlaceBarCode,b.FNumber PlaceName, ");
            commandText.Append("  a.Floor,a.ItemSku,  a.StickLen,a.Stick,a.FQty,  ");
            commandText.Append("  case  SUBSTRING(a.HelperCode,1,1)  when 'A' then '1' WHEN 'B' THEN '2' end PkgsIsRegular   ");
            commandText.Append("  from stk_Package a   left join t_StockPlace b on a.PosiID = b.FSPID    ");
            commandText.Append(" where PkgsBarCode ='" + PkgsBarCode + "'   ");

            //commandText.Append("   select a.PkgsBarCode,a.HelperCode,b.PlaceBarCode,b.FNumber PlaceName,a.Floor,a.ItemSku,");
            //commandText.Append("  a.StickLen,a.Stick,a.FQty, case  isnull(c.PkgsID,0)");
            //commandText.Append("  when 0 then '1' else '2' end PkgsIsRegular   ");
            //commandText.Append("  from stk_Package a ");
            //commandText.Append("  left join t_StockPlace b on a.PosiID = b.FSPID ");
            //commandText.Append("  left join v_bdc c on a.PkgsID = c.PkgsID ");
            //commandText.Append("  where PkgsBarCode ='" + PkgsBarCode + "'  ");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (ds != null && ds.Tables[0].Rows.Count>0)
            {
                result = true;

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    PlaceBarCode = dr["PlaceBarCode"].ToString().Trim(); 
                    PlaceName = dr["PlaceName"].ToString().Trim();
                    Floor = dr["Floor"].ToString().Trim();
                    Fsku = dr["ItemSku"].ToString().Trim(); if (Fsku == null || Fsku == "") { return false; };
                    StickLen = dr["StickLen"].ToString().Trim(); if (StickLen == null || StickLen == "") { return false; };
                    Stick = dr["Stick"].ToString().Trim(); if (Stick == null || Stick == "") { return false; };
                    FQty = dr["FQty"].ToString().Trim(); if (FQty == null || FQty == "") { return false; };
                    PkgsIsRegular = dr["PkgsIsRegular"].ToString().Trim();
                    HelperCode = dr["HelperCode"].ToString().Trim(); if (HelperCode == null || HelperCode == "") { return false; } ;
                }
            }
            return result;
        }


        /// <summary>
        /// 查询扫码数量
        /// </summary>
        /// <param name="FBillNo"></param>
        /// <returns></returns>
        public int GetScanQty(string FBillNo)
        {
            int i = 0;
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();

            sql.Append("  select COUNT(0) num from t_SEOutStockDetail where FBillNo ='" + FBillNo + "'  ");          
            sql.Append(" and flag=1 ");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.QueryTable(sql.ToString());
                if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        i = Convert.ToInt32(dr["num"].ToString());
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return i;
        }
        
        /// <summary>
        /// 查询未扫码数量
        /// </summary>
        /// <param name="FBillNo"></param>
        /// <returns></returns>
        public int SelScanQty(string FBillNo)
        {
            int i = 0;
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();

            sql.Append("  select COUNT(0) num from t_SEOutStockDetail where FBillNo ='" + FBillNo + "'  ");
            sql.Append(" and flag=0 ");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.QueryTable(sql.ToString());
                if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        i = Convert.ToInt32(dr["num"].ToString());
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return i;
        }
        //更新条码
        //state:1:根长、sku、定尺/不定尺、数量不匹配条件;state = 2;条码未在销售订单中，且已被锁库
        // state = 5;//state=1该根长、sku、定尺/不定尺、数量的条码个数已经达到发货数量，不能继续扫码  
        //state = 3条码未在销售订单中但条码未被锁库 ;state = 4出库成功;state = 6出库失败
        public int UpdateSEOutStock(string PkgsBarCode, string FBillNO, string HelperCode, string FSku, string StickLen, string FQty, string PkgsIsRegular, string ID, string plname)
        {
            int state = 0;
            int i = 0;
            int   JudgeQtyresult = 0;
            string Ntime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

            if (PkgsIsRegular == "1")
            {
                i = JudgeSku(PkgsBarCode, FSku, StickLen, FQty, FBillNO); //i=0根长、sku、数量不符合条件，i=1根长、sku、数量符倍长
                if (i > 0)
                { 
                     JudgeQtyresult = JudgeQty(FSku, StickLen, FQty, FBillNO,PkgsIsRegular);
                }
                 else if (i == 0)
                {
                    return 1;//state=1根长、sku、定尺/不定尺、数量不匹配条件
                }                
            }
            else if (PkgsIsRegular == "2")
            {
                i = JudgeSkuUnRegular(PkgsBarCode, FSku, FQty, FBillNO); //i=0根长、sku、数量不符合条件，i=1根长、sku、数量符合条件
                if(i>0)
                {
                    JudgeQtyresult =  JudgeQty(FSku, StickLen, FQty, FBillNO,PkgsIsRegular);
                }
                else if (i == 0)
                {
                    return 1;//state=1根长、sku、定尺/不定尺、数量不匹配条件
                }
                
            }
            if (JudgeQtyresult > 0)
            {
                int j = JudgeSEOutStockDetail(PkgsBarCode, FBillNO); //j = 0 条码不在销售出库单中；j=1条码在销售出库单中
                if (j == 0)
                {
                    int k = JudgeLockDetail(PkgsBarCode); //k = 0 条码已被锁库；k=1条码未被锁库       
                    if (k == 0)
                    {
                        state = 2; // state = 2;条码未在销售订单中，且已被锁库
                    }
                    else
                    {
                        state = 3; //state = 3条码未被锁库 

                    }
                }
                else //条码在出库单中
                {
                    string sqls = "select flag from t_SEOutStockDetail where  FBillNo = '" + FBillNO + "' and   PkgsBarCode ='" + PkgsBarCode + "'";//t_SEOutStockDetail set flag = 1,Operator ='" + ID + "',OperatorTime ='" + Ntime + "' where  FBillNo = '" + FBillNo + "' and   PkgsBarCode ='" + PkgsBarCode + "' and flag = '0'
                    DataTable der = DbHelperSQL.QueryTable(sqls);
                    if (der != null && der.Rows.Count > 0)
                    {
                        if (der.Rows[0]["flag"].ToString() == "1")
                        {
                            state = 67;
                        }
                        else
                        {
                            int l = 0;
                            l = UpdatePkgsBarCode(PkgsBarCode, FBillNO, ID, Ntime, plname);
                            if (l > 0)
                            {
                                state = 4;  // state = 4出库成功
                            }
                            else if (l == 0)
                            {
                                state = 6;  // state = 6出库失败
                            }
                        }
                    }
                    else
                    {
                        int l = 0;
                        l = UpdatePkgsBarCode(PkgsBarCode, FBillNO, ID, Ntime, plname);
                        if (l > 0)
                        {
                            state = 4;  // state = 4出库成功
                        }
                        else if (l == 0)
                        {
                            state = 6;  // state = 6出库失败
                        }
                    }
                }
            }
            else
            {             
                 state = 5;//state=1该根长、sku、定尺/不定尺、数量的条码个数已经达到要求，不能继续扫码               
            }
           
            return state;
        }
        //排后按钮事件
        public int Updatepx(string PkgsBarCode, string FBillNO)
        {
            int l = 0;
            StringBuilder sql = new StringBuilder();
            string sqls = "select ISNULL(MAX(pxnum),0)+1 as pxnum from t_SEOutStockDetail where FBillNo='" + FBillNO + "'";
            DataTable de = DbHelperSQL.QueryTable(sqls);

            sql.Append("update t_SEOutStockDetail set PXnum='" + de.Rows[0]["pxnum"].ToString() + "' where PkgsBarCode='" + PkgsBarCode + "' and FBillNo='" + FBillNO + "'");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                l = DbHelperSQL.ExecuteSql(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return l;
        }
        //判断替换的条码是否为同一销售出库单
        public bool ISExist(string PkgsBarCode,string oldPkgsBarCode, string FBillNo)
        {
            StringBuilder sql = new StringBuilder();
            bool result = false;
            DataTable dt = new DataTable();
            sql.Append("select PkgsBarCode from t_SEOutStockDetail where FBillNo = '" + FBillNo + "'  and PkgsBarCode in('" + PkgsBarCode + "','" + oldPkgsBarCode + "')");

            try {
                dt = DbHelperSQL.QueryTable(sql.ToString());
                if (dt.Rows.Count > 1)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// 金蝶传出数据
        /// </summary>
        /// <param name="FBillNo"></param>
        /// <param name="txt"></param>
        /// <returns></returns>
        public bool jdSEOoutStock(string FBillNo, string userid, out string txt)
        {
            //调用金蝶接口方法out 
            WebReference.jdWebService seo = new WebReference.jdWebService();
            txt = "";
            int falg = 0;
            int flfalg = 0;
            bool lb_result = false;
            FBillNo = FBillNo.Trim();
            bool result = CheckSEOoutStock(FBillNo, out txt);
            if (result == true)
            {
                //调用金蝶接口方法
                int nums = seo.CCPXT_INSERT_ICStockBillck("CCPXT_INSERT_ICStockBillck", "" + FBillNo + "," + userid + "");
                if (nums > 0)
                {
                    lb_result = true;
                }
                else
                {
                    lb_result = false;
                }
                return lb_result;
                #region
//                //查询金蝶分录id的最大值(查询存储过程得出结果)
//                int fitenid = seo.Maxfitemid("GetICMaxNum", "ICStockBill,1," + userid + "");
//                //查询销售出库单主表信息并往金蝶传输数据
//                string sql = "select * from t_SEOutStock WHERE FBillNo ='" + FBillNo + "'";
//                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
//                DataTable dd = DbHelperSQL.QueryTable(sql);
//                if (dd.Rows.Count > 0)
//                {
//                    falg = seo.insertICStockBill(fitenid.ToString(), dd.Rows[0]["FBillNo"].ToString(), "21", DateTime.Now.ToString("yyyy-MM-dd"), "", dd.Rows[0]["FDeptID"].ToString(), 
//                        dd.Rows[0]["FEmpID"].ToString(), dd.Rows[0]["FCustID"].ToString(), dd.Rows[0]["XFManagerID"].ToString(),
//                        dd.Rows[0]["XSManagerID"].ToString(), dd.Rows[0]["FBillerID"].ToString(), dd.Rows[0]["FSalType"].ToString(),
//                        "1", "1", dd.Rows[0]["FCheckerID"].ToString(),
//                        dd.Rows[0]["FCheckDate"].ToString(), "2", userid, DateTime.Now.ToString("yyyy-MM-dd"), "12530", 
//                        dd.Rows[0]["FExplanation"].ToString(), dd.Rows[0]["FManagerID"].ToString(), "81",
//                        DateTime.Now.ToString("yyyy-MM-dd"), "", dd.Rows[0]["XSalesArea"].ToString(),
//                        dd.Rows[0]["Xexplanation"].ToString(), dd.Rows[0]["XReceivingUnit"].ToString(),DateTime.Parse(dd.Rows[0]["XPushSingletime"].ToString()).ToString("yyyy-MM-dd"),
//                        dd.Rows[0]["Xconsignee"].ToString(), dd.Rows[0]["Fphone"].ToString(),
//                        dd.Rows[0]["Ffax"].ToString(), dd.Rows[0]["Fadd"].ToString(), userid, DateTime.Now.ToString("yyyy-MM-dd"));

//                    if (falg == 1)
//                    {
//                        int d = 0;
//                        //查询销售出库单分录表的信息，汇总同一个物料合并在一起传到金蝶出库单分录表
//                        string sqls = @"select Fprice,FSku,FBatchNo,FUnitID,FItemID,XOughtQty,sum(XActualQty) XActualQty,
//                                          sum(XPiece) XPiece,sum(FAmount) FAmount,DFEntryID,DFInterID,FSourceBillNo,StockID,
//                                          XLogisticsModeName,sum(XRowWeight) XRowWeight from 
//                                          (select SUBSTRING(a.FSku,0,CHARINDEX('__',a.FSku)) FSku,FBatchNo,FUnitID,FItemID,
//                                          a.XOughtQty,sum(isnull(b.Total,0)) XActualQty,sum(isnull(a.FPrice,0)) FPrice,
//                                          sum(isnull(b.Total*a.FPrice,0)) FAmount,a.DFEntryID,a.DFInterID,a.FSourceBillNo,a.OrderNO,
//                                         (select StockID from stk_Package s where s.PkgsBarCode=a.PkgsBarCode) as StockID,
//                                          XLogisticsModeName,sum(isnull(b.Total*a.XUnitWeight,0)) XRowWeight,sum(isnull(b.num,0)) XPiece FROM (
//                                          SELECT FBillNo ,FEntryID,sum(case flag when 1 then 1 else 0 end) num,
//                                          SUM(  case flag when 1 then FQty else '0' end ) Total FROM t_SEOutStockDetail WHERE FBillNo ='"+FBillNo+@"'  
//                                          GROUP BY FBillNo,FEntryID,FSku) b
//                                          LEFT JOIN 
//                                          t_SEOutStockEntry a 
//                                          on a.FBillNo = b.FBillNo and a.FEntryID = b.FEntryID 
//                                          group by SUBSTRING(a.FSku,0,CHARINDEX('__',a.FSku)),FBatchNo,FUnitID,FItemID,a.XOughtQty,a.DFEntryID,a.DFInterID,
//                                          a.FSourceBillNo,a.PkgsBarCode,XLogisticsModeName,a.OrderNO,a.FPrice) as gg  
//                                          group by FSku,FBatchNo,FUnitID,FItemID,XOughtQty,DFEntryID,DFInterID,FSourceBillNo,StockID,XLogisticsModeName,OrderNO,FPrice,XPiece";

//                            DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
//                            DataTable dds = DbHelperSQL.QueryTable(sqls);
//                            if (dds.Rows.Count > 0)
//                            {
//                                for (int i = 0; i < dds.Rows.Count; i++)
//                                {
//                                    string fnote = "";
//                                    string sql3 = "select FNote from t_SEOutStockEntry f where f.FBillNo = '" + FBillNo +"'  and FItemID = '" + dds.Rows[i]["FItemID"].ToString() + "' and FNote <> '' group by FNote";
//                                    DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
//                                    DataTable de = DbHelperSQL.QueryTable(sql3);
//                                    if (de.Rows.Count > 0)
//                                    {
//                                        fnote = de.Rows[0]["FNote"].ToString();
//                                    }
                                    
//                                    flfalg = seo.insertICStockBillEntry(fitenid.ToString(), (i + 1).ToString(), dds.Rows[i]["FItemID"].ToString(),
//                                        dds.Rows[i]["XOughtQty"].ToString(), dds.Rows[i]["XActualQty"].ToString(), dds.Rows[i]["FBatchNo"].ToString(),
//                                        fnote, dds.Rows[i]["FUnitID"].ToString(), dds.Rows[i]["XActualQty"].ToString(),
//                                        dds.Rows[i]["XOughtQty"].ToString(), dds.Rows[i]["DFEntryID"].ToString(), dds.Rows[i]["Fprice"].ToString(),
//                                        dds.Rows[i]["FAmount"].ToString(), "81", dds.Rows[i]["DFInterID"].ToString(),
//                                        dds.Rows[i]["FSourceBillNo"].ToString(), dds.Rows[i]["DFInterID"].ToString(), dds.Rows[i]["DFEntryID"].ToString(),
//                                        dds.Rows[i]["FSourceBillNo"].ToString(), dds.Rows[i]["StockID"].ToString(), "14036",
//                                        dds.Rows[i]["XLogisticsModeName"].ToString(), dds.Rows[i]["XRowWeight"].ToString(), dds.Rows[i]["XPiece"].ToString());
//                                    if (flfalg > 0)
//                                    {
//                                        d++;
//                                        int ddf= seo.updateICInventory(dds.Rows[i]["FItemID"].ToString(), dds.Rows[i]["StockID"].ToString(), dds.Rows[i]["XActualQty"].ToString());
//                                    }
//                                }
//                                if (d > 0)
//                                {

//                                    return lb_result = true;
//                                }
//                                else
//                                {

//                                    return lb_result = false;
//                                }
//                            }
//                            else
//                            {

//                                return lb_result = false;
//                            }
//                    }
//                    else if (falg ==2)
//                    {
//                        lb_result = true;
//                    }
//                    else
//                    {

//                        return lb_result = false;
//                    }
//                }
                #endregion

            }
            return lb_result;
        }
        /// <summary>
        /// 完成出库,out string txt out
        /// </summary>
        /// <param name="FBillNo"></param>
        /// <returns></returns>
        public bool FinishSEOoutStock(string FBillNo,string userid,out string txt)
        {
            txt = "";
            bool lb_result = false;
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();
            DataTable dt1 = new DataTable();
            DataTable dt2 = new DataTable();
            DataTable dt3 = new DataTable();
            DataSet dt4 = new DataSet();
            DataSet dt5 = new DataSet();
            List<string> commandText = new List<string>();
            int j = 0;

            //bool result = true;
            FBillNo = FBillNo.Trim();
            bool result = CheckSEOoutStock(FBillNo, out txt);

            if (result == true)
            {
                ////调用金蝶接口传输数据到金蝶
                //bool resultjd = jdSEOoutStock(FBillNo,userid, out txt);
                //if (resultjd == false)
                //{
                //    return lb_result = false;
                //}
                
                //------------------------------------------------------
                //获取预出库表中的条码
                dt = GetPkgsBarCodeTable(FBillNo);
                if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        string PkgsBarCode = dr["PkgsBarCode"].ToString(); if (PkgsBarCode != null && PkgsBarCode != "") { } else { return false; }
                        commandText.Add("update stk_Package set  SaleBillNo ='" + FBillNo + "' ,PkgsStatus = '2' where PkgsBarCode ='" + PkgsBarCode + "'");
                    }
                    //获取并更新销售出库单中“实际出库数量”、实际出库数量、件数、金额、行重量 锁库情况 
                    dt1 = GetSEOutStock(FBillNo); //更新销售出库单
                    if (dt1 != null)
                    {
                        for (int i = 0; i < dt1.Rows.Count; i++)
                        {
                            decimal XActualQty = 0; int XPiece = 0; decimal FAmount = 0;
                            decimal XRowWeight = 0; decimal XShippedQty = 0; string ll_FBillNo = ""; string ll_FEntryID = "";
                            string DFInterID = ""; string DFEntryID = ""; string FDetailID = "";

                            XActualQty = Convert.ToDecimal(dt1.Rows[i]["XActualQty"].ToString());//实际出库数量
                            XPiece = Convert.ToInt32(dt1.Rows[i]["XPiece"].ToString()); //件数
                            FAmount = Convert.ToDecimal(dt1.Rows[i]["FAmount"].ToString());//金额
                            XRowWeight = Convert.ToDecimal(dt1.Rows[i]["XRowWeight"].ToString());//行重量
                            XShippedQty = XActualQty;//出库数量
                            ll_FBillNo = dt1.Rows[i]["FBillNo"].ToString();
                            ll_FEntryID = dt1.Rows[i]["FEntryID"].ToString();

                            commandText.Add(" update    t_SEOutStockEntry set XActualQty = '" + XActualQty + @"'  ,XPiece = '" + XPiece + @"',FAmount= '" + FAmount + @"',
                                         XRowWeight='" + XRowWeight + @"',XShippedQty = '" + XShippedQty + @"' where  FBillNo = '" + FBillNo + @"'
                                         and FEntryID ='" + ll_FEntryID + @"' ");
                        }

                    }
                    //获取并更新销售订单中“实际出库数量”锁库情况

                    dt2 = GetProduction(FBillNo);

                    if (dt2 != null)
                    {
                        foreach (DataRow dr2 in dt2.Rows)
                        {

                            string l1_FInterID = dr2["DFInterID"].ToString();
                            string l1_DFEntryID = dr2["DFEntryID"].ToString().Trim();
                            string l1_Fqty = dr2["Fqty"].ToString(); //应发数量
                            string li_FStockQty = Convert.ToString(Convert.ToDecimal(dr2["FStockQty"].ToString()) - Convert.ToDecimal(dr2["AbanINStockQty"].ToString())); // 出库数量
                            decimal li_XSendquantity = Convert.ToDecimal(l1_Fqty) - Convert.ToDecimal(li_FStockQty); ;//待发量=应发数量-出库数量；
                            commandText.Add("   update t_SEOrderEntry set FStockQty = '" + li_FStockQty + "',XSendquantity = '" + li_XSendquantity + "' where FInterID = '" + l1_FInterID + "' and FEntryID = '" + l1_DFEntryID + "'");

                        }

                    }

                    //更新锁库页面中的备注（数量）
                    dt4 = GetLockNum(FBillNo);
                    if (dt4 != null)
                    {
                        foreach (DataRow dr4 in dt4.Tables[0].Rows)
                        {
                            string l1_FInterID4 = dr4["DFInterID"].ToString();
                            string li_Note4 = dr4["total"].ToString();
                            string li_FEntryID4 = dr4["DFEntryID"].ToString();
                            string li_FDetailID = dr4["FDetailID"].ToString();
                            //string li_PkgsIsRegular4 = dr4["PkgsIsRegular"].ToString();
                            //string li_PkgsIsRegular4 = dr4["F"].ToString();

                            commandText.Add("   update t_SEOrderDetail set note ='" + li_Note4 + "' where FInterID ='" + l1_FInterID4 + "' and FEntryID='" + li_FEntryID4 + "' and FDetailID='" + li_FDetailID + "'");

                        }
                    }


                    //备份出库日志
                    commandText.Add(@"insert into t_SEOutStockDetailLog(
                        FBillNo ,Fdate ,Fry,PkgsID,PkgsBarCode,FSku,StickLen,Stick,
                        FQty,StockID,PosiID,FLOOR,Fnumber,PkgsIsRegular,states,flag,FSourceBillNo,note,DFEntryID,DFInterID,FDetailID
                        )
                        select FBillNo ,Fdate ,Fry,PkgsID,PkgsBarCode,FSku,StickLen,Stick,
                        FQty,StockID,PosiID,FLOOR,Fnumber,PkgsIsRegular,states,flag,FSourceBillNo,note,DFEntryID,DFInterID,FDetailID
                         from  t_SEOutStockDetail where FBillNo ='" + FBillNo + "'");

                    //获取未扫码的,更新状态为在库,flag=0 未扫描；flag =2 被替换的，未扫描；flag=4，在预出库中删除的，未扫描；flag=3，丢失的，未扫描
                    dt3 = GetLock(FBillNo);
                    if (dt != null)
                    {
                        foreach (DataRow dr in dt3.Rows)
                        {
                            string ls_PkgsBarCode = dr["PkgsBarCode"].ToString().Trim();
                            string flag = dr["flag"].ToString().Trim();
                            string ls_HelperCode = dr["HelperCode"].ToString().Trim();
                            string ls_newPkgsBarCode = "";
                            string ls_newHelperCode = "";

                            if (flag == "2")
                            {
                                bool lb_return5 = GetOrient(ls_PkgsBarCode, out  ls_newPkgsBarCode, out  ls_newHelperCode);
                                if (lb_return5 == true)
                                {
                                    commandText.Add(" update t_SEOutStockEntry set PkgsCode ='" + ls_newHelperCode + "' ,PkgsBarCode ='" + ls_newPkgsBarCode + "' where PkgsCode ='" + ls_HelperCode + "' and PkgsBarCode ='" + ls_PkgsBarCode + "' and FBillNo ='" + FBillNo + "'");
                                    commandText.Add(" update stk_Package set PkgsStatus=0,HoldBillNo=null,Holder=null,HoldTime=null where PkgsBarCode='" + ls_PkgsBarCode + "'");
                                }
                            }
                            else if (flag == "0")
                            {
                                commandText.Add(" delete from t_SEOutStockEntry where PkgsCode ='" + ls_HelperCode + "' and PkgsBarCode ='" + ls_PkgsBarCode + "' and FBillNo ='" + FBillNo + "'");
                                commandText.Add(" update stk_Package set PkgsStatus=1 where PkgsBarCode='" + ls_PkgsBarCode + "'");
                            }
                            //else if (flag == "3")
                            //{
                            //    commandText.Add(" delete from t_SEOrderDetail where DCtype='0' and PkCode in (select PkCode from t_SEOutStockEntry where PkgsBarCode ='" + ls_PkgsBarCode + "')");
                            //}
                            //else if (flag == "4")
                            //{
                            //    commandText.Add(" delete from t_SEOrderDetail where DCtype='0' and  PkCode in (select PkCode from t_SEOutStockEntry where PkgsBarCode ='" + ls_PkgsBarCode + "')");
                            //}
                            commandText.Add(" delete from t_SEOutStockDetail where FBillNo ='" + FBillNo + "' and  PkgsBarCode='" + ls_PkgsBarCode + "'");

                        }
                    }

                    commandText.Add(" update t_SEOutStock set States ='4'  where FBillNo = '" + FBillNo + "' and  States ='2'");
                    commandText.Add(" update t_SEOutStockEntry  set States ='4'  where FBillNo = '" + FBillNo + "' and  States ='2' ");

                    try
                    {
                        DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                        int i = DbHelperSQL.ExecuteSqlTran(commandText);
                        if (i > 0)
                        {
                            List<string> commandText1 = new List<string>();
                            lb_result = true;
                            //完成销售出库之后订单数量与发货数量相等时，状态自动完成状态
                            string sqls = @"select SUM(cast(Fqty as decimal(18,4))) as Fqty,SUM(FStockQty) as FStockQty,FEntryID from t_SEOrderEntry 
                               where FInterID=(select DFInterID from (select ROW_NUMBER() over(partition by DFInterID  order by DFInterID) as rows ,DFInterID from t_SEOutStockEntry where FBillNo='" + FBillNo + "') d where d.rows=1) group by FEntryID;";
                            DataTable df = DbHelperSQL.QueryTable(sqls);
                            decimal sumFqty = 0;
                            decimal sumStockQty = 0;
                            if (df.Rows.Count > 0)
                            {
                                for (int h = 0; h < df.Rows.Count; h++)
                                {
                                    if (decimal.Parse(df.Rows[h]["Fqty"].ToString()).Equals(decimal.Parse(df.Rows[h]["FStockQty"].ToString())))
                                    {
                                        sumFqty = sumFqty + decimal.Parse(df.Rows[h]["Fqty"].ToString());
                                        sumStockQty = sumStockQty + decimal.Parse(df.Rows[h]["FStockQty"].ToString());
                                    }
                                    else
                                    {
                                        sumFqty = sumFqty + decimal.Parse(df.Rows[h]["Fqty"].ToString());
                                        sumStockQty = sumStockQty + decimal.Parse(df.Rows[h]["FStockQty"].ToString());
                                    }
                                }
                                if (sumFqty.Equals(sumStockQty))
                                {
                                    commandText1.Add(" update t_SEOrder  set  States = '3' where FInterID in (select DFInterID from (select ROW_NUMBER() over(partition by DFInterID  order by DFInterID) as rows ,DFInterID from t_SEOutStockEntry where FBillNo='" + FBillNo + "') d where d.rows=1)");
                                    commandText1.Add(" update t_SEOrderEntry  set  checkstatus = '3'  where FInterID in (select DFInterID from (select ROW_NUMBER() over(partition by DFInterID  order by DFInterID) as rows ,DFInterID from t_SEOutStockEntry where FBillNo='" + FBillNo + "') d where d.rows=1)");
                                }
                                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                                int T = DbHelperSQL.ExecuteSqlTran(commandText1);
                                if (T > 0)
                                { lb_result = true; }
                            }

                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }

                }
            }
            return lb_result;
        }

        //更新锁库页面中的已销售（数量）
        public DataSet GetLockNum(string FBillNo)
        {
            DataSet ds = new DataSet();
            StringBuilder sql = new StringBuilder();

            sql.Append("  select b.*,  SUM( case flag when 1 then a.FQty else '0' end )  total  from   ( select  DFInterID ,DFEntryID,FDetailID   from t_SEOutStockDetail ");
            sql.Append("   where FBillNo =  '" + FBillNo + "'  group by DFInterID ,DFEntryID,FDetailID) b ");
            sql.Append("   left join t_SEOutStockDetail a  ");
            sql.Append("   on a.DFInterID = b.DFInterID and a.DFEntryID = b.DFEntryID and a.FDetailID=b.FDetailID");
            sql.Append("   group by  b.DFInterID ,b.DFEntryID,b.FDetailID");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public bool GetOrient(string oldPkgsBarCode, out string ls_newPkgsBarCode, out string ls_newHelperCode)
        {
            bool lb_return = true;
            DataSet ds = new DataSet();
            StringBuilder sql = new StringBuilder();
             ls_newPkgsBarCode = "";
             ls_newHelperCode = "";

            sql.Append("select PkgsBarCode,HelperCode from  t_SEOutStockDetail where note like '" + oldPkgsBarCode + "%' and flag ='1'");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(sql.ToString());

                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        ls_newPkgsBarCode = dr["PkgsBarCode"].ToString().Trim();
                        if (ls_newPkgsBarCode != null && ls_newPkgsBarCode != "") { } else { return false; }
                        ls_newHelperCode = dr["HelperCode"].ToString().Trim();
                        if (ls_newHelperCode != null && ls_newHelperCode != "") { } else { return false; }
                    }
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lb_return;
        }


        //将找不到的条码更新到t_SEOutStockDetail表中
        public int UpdateNotFindCode(string oldPkgsBarCode, string FBillNo, string username)
        {
            int i = 0;
            List<string> sql =new List<string>();
            string ntime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string sqls = "select PieceLen,DFEntryID,DFInterID from t_SEOutStockEntry where PkgsBarCode ='" + oldPkgsBarCode + "' and FBillNo ='" + FBillNo + "'";
            DataTable der = DbHelperSQL.QueryTable(sqls);
            if (der.Rows.Count > 0)
            {
                sql.Add(" update t_SEOrderEntry  set  XTFqty=(isnull(XTFqty,0)-" + der.Rows[0]["PieceLen"].ToString().Trim() + ")  where FInterID='" + der.Rows[0]["DFInterID"].ToString().Trim() + "' and FEntryID='" + der.Rows[0]["DFEntryID"].ToString().Trim() + "'");

            }
            sql.Add("update t_SEOutStockDetail set flag='3' ,note='库管:" + username + ",在时间:" + ntime + ",消件条码:" + oldPkgsBarCode + " 未找到'  where FBillNo ='" + FBillNo + "' and PkgsBarCode ='" + oldPkgsBarCode + "' and flag='0'");
            sql.Add(" update stk_Package set PkgsStatus=0,HoldBillNo=null,Holder=null,HoldTime=null where PkgsBarCode='" + oldPkgsBarCode + "'");
            sql.Add(" delete from t_SEOrderDetail where PkCode in (select PkCode from t_SEOutStockEntry where PkgsBarCode ='" + oldPkgsBarCode + "' and FBillNo ='" + FBillNo + "')"); 
            sql.Add(" delete from t_SEOutStockEntry where PkgsBarCode ='" + oldPkgsBarCode + "' and FBillNo ='" + FBillNo + "'");
            
             try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                i = DbHelperSQL.ExecuteSqlTran(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return i;
        }
        //将消件条码更新到t_SEOutStockDetail表中
        public int UpdateTUIFindCode(string oldPkgsBarCode, string FBillNo, string username)
        {
            int i = 0;
            List<string> sql = new List<string>();
            string ntime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string sqls = "select PieceLen,DFEntryID,DFInterID from t_SEOutStockEntry where PkgsBarCode ='" + oldPkgsBarCode + "' and FBillNo ='" + FBillNo + "'";
            DataTable der = DbHelperSQL.QueryTable(sqls);
            if (der.Rows.Count > 0)
            {
                sql.Add(" update t_SEOrderEntry  set  XTFqty=(isnull(XTFqty,0)-" + der.Rows[0]["PieceLen"].ToString().Trim() + ")  where FInterID='" + der.Rows[0]["DFInterID"].ToString().Trim() + "' and FEntryID='" + der.Rows[0]["DFEntryID"].ToString().Trim() + "'");

            }
            sql.Add("update t_SEOutStockDetail set flag='4' ,note='库管:" + username + ",在时间:" + ntime + ",消件条码:" + oldPkgsBarCode + " 已取消'  where FBillNo ='" + FBillNo + "' and PkgsBarCode ='" + oldPkgsBarCode + "' and flag='0'");
            sql.Add(" update stk_Package set PkgsStatus=0,HoldBillNo=null,Holder=null,HoldTime=null where PkgsBarCode='" + oldPkgsBarCode + "'");
            sql.Add(" delete from t_SEOrderDetail where PkCode in (select PkCode from t_SEOutStockEntry where PkgsBarCode ='" + oldPkgsBarCode + "' and FBillNo ='" + FBillNo + "')");
            sql.Add(" delete from t_SEOutStockEntry where PkgsBarCode ='" + oldPkgsBarCode + "' and FBillNo ='" + FBillNo + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                i = DbHelperSQL.ExecuteSqlTran(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return i;
        }

        //获取在预出库中被删除的条码列表states=4flag = '4'
        public DataSet GetPkgsBarCodeList(string FBillNo)
        {
            bool result = false;
            List<string> sql = new List<string>();
            StringBuilder commandText = new StringBuilder();
            DataSet ds = new DataSet();
            commandText.Append("select PkgsBarCode from t_SEOutStockDetail  where FBillNo ='" + FBillNo + "' and flag = '4' and states='4'");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        //获取并更新销售出库单中“实际出库数量”、实际出库数量、件数、金额、行重量 锁库情况 
        public DataTable GetSEOutStock(string FBillNo)
        {
            StringBuilder commandText = new StringBuilder();
            DataTable dt = new DataTable();

            //commandText.Append("select a.DFInterID,a.DFEntryID,a.FDetailID ,a.FBillNo,a.FEntryID,a.FEntryIDLenth,a.FSku,a.XOughtQty,a.FPrice, a.XActualQty FXActualQty,");
            //commandText.Append("   a.XPiece FXPiece,  a.FAmount FFAmount, a.XRowWeight FXRowWeight,isnull(b.Total,0) XActualQty,");
            //commandText.Append("   isnull(b.num,0) XPiece,isnull(b.Total*a.FPrice,0) FAmount,  isnull(b.Total*a.XUnitWeight,0) XRowWeight   FROM (   ");
            //commandText.Append(" SELECT FBillNo ,FSku,COUNT(0) num,SUM(FQty) Total FROM t_SEOutStockDetail WHERE FBillNo ='" + FBillNo + "' ");
            //commandText.Append(" GROUP BY FBillNo,FSku ");
            //commandText.Append(" ) b");
            //commandText.Append(" LEFT JOIN t_SEOutStockEntry a on a.FBillNo = b.FBillNo and a.FSku = b.FSku");
            
            commandText.Append("  select a.DFInterID,a.DFEntryID,a.FDetailID ,a.FBillNo,a.FEntryID,");
            commandText.Append(" a.FEntryIDLenth,a.FSku,a.XOughtQty,a.FPrice, a.XActualQty FXActualQty,");
            commandText.Append("  a.XPiece FXPiece,  a.FAmount FFAmount, a.XRowWeight FXRowWeight,");
            commandText.Append("  isnull(b.Total,0) XActualQty,   isnull(b.num,0) XPiece,isnull(b.Total*a.FPrice,0) FAmount, ");
            commandText.Append("  isnull(b.Total*a.XUnitWeight,0) XRowWeight   FROM (    ");
            commandText.Append("  SELECT FBillNo ,FEntryID,sum(case flag when 1 then 1 else 0 end) num,");
            commandText.Append("  SUM(  case flag when 1 then FQty else '0' end ) Total FROM t_SEOutStockDetail");
            commandText.Append("   WHERE FBillNo ='" + FBillNo + "'  GROUP BY FBillNo,FEntryID ");
            commandText.Append("    ) b ");
            commandText.Append("   LEFT JOIN t_SEOutStockEntry a on a.FBillNo = b.FBillNo and a.FEntryID = b.FEntryID");


            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.QueryTable(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        //获取并更新销售订单中“实际出库数量”锁库情况

        public DataTable GetProduction(string FBillNo)
        {
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();
            
          
            sql.Append(" select b.*,c.Fqty from (");
            sql.Append("	 select b.*,  SUM( case flag when 1 then a.FQty else '0' end )  FStockQty,(select SUM(isnull(AbanINStockQty,0)) from t_SEOrderEntry where FInterID=b.DFInterID and FEntryID=b.DFEntryID) as AbanINStockQty  from   (");
            sql.Append("	 select  DFInterID ,DFEntryID");
            sql.Append("	 from t_SEOutStockDetail ");
            sql.Append("			   where FBillNo =  '" + FBillNo + "'  group by DFInterID ,DFEntryID) b ");
            sql.Append("			   left join t_SEOutStockDetail a  ");
            sql.Append("			   on a.DFInterID = b.DFInterID and a.DFEntryID = b.DFEntryID");
            sql.Append("			   group by  b.DFInterID ,b.DFEntryID) b ");
            sql.Append(" left join t_SEOrderEntry c");
            sql.Append(" on c.FInterID = b.DFInterID and c.FEntryID = b.DFEntryID  ");

                    
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.QueryTable(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        //获取未扫码的
        public DataTable GetLock(string FBillNo)
        {
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();
            sql.Append("select PkgsBarCode,HelperCode,flag  from t_SEOutStockDetail where FBillNo ='" + FBillNo + "' and flag !=1 ");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

                dt = DbHelperSQL.QueryTable(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        //获取预出库表中的条码
        public DataTable GetPkgsBarCodeTable(string FBillNo)
        {
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();
            List<string> commandText = new List<string>();
            sql.Append("select PkgsBarCode from t_SEOutStockDetail where FBillNo ='" + FBillNo + "' and flag = '1'");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.QueryTable(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        //判断是否可以扫码出库
        public bool CheckSEOoutStock(string FBillNo, out string txt)
        {
            txt = "";
            if (IsCheckSEOoutStock(FBillNo)) { } else { txt = "未审核或已完成，不能扫码！"; return false; };
            //if (IsQtySEOoutStock(FBillNo)) { } else { txt = "该出库单未出库完，不能完成；"; return false; };
            return true;
        }

        public bool IsQtySEOoutStock(string FBillNo)
        {

            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();
            bool result = false;

            int qty = 0;
            int scanqty = 0;
            sql.Append(" select a.FBillNo,ISNULL(a.qty,0) qty,isnull(b.scanqty,0) scanqty  from (");
            sql.Append("  select FBillNo, COUNT(0) qty from  t_SEOutStockDetail where states = '1' group by FBillNo) a");
            sql.Append("  left join  (");
            sql.Append(" select FBillNo, COUNT(0) scanqty from  t_SEOutStockDetail where flag = '1' group by FBillNo) b");
            sql.Append(" on a.FBillNo = b.FBillNo             ");
            sql.Append("  where  a.FBillNo ='" + FBillNo + "' ");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.QueryTable(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    qty = Convert.ToInt32(dr["qty"].ToString());
                    scanqty = Convert.ToInt32(dr["scanqty"].ToString());
                    if (qty - scanqty == 0)
                    { result = true; }
                }
            }
            return result;
        }

        //判断状态，是否可以扫描出库
        public bool IsCheckSEOoutStock(string FBillNo)
        {

            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();
            string ls_states = "";

            sql.Append("select States from t_SEOutStock where  FBillNo  = '" + FBillNo + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.QueryTable(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    ls_states = dr[0].ToString().Trim();
                }
                if (ls_states == "2") { } else { return false; }
            }

            return true;
        }

        //判断sku、根长、根是否符合1定尺，2不定尺
        public int JudgeSku(string PkgsBarCode, string FSku, string StickLen, string FQty, string FBillNO)
        {
            int i = 0;
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();

         
            //sql.Append(" select count(0) num  from ( ");
            //sql.Append(" select FBillNo,FSku,StickLen,FQty, ");
            //sql.Append("  case ISNULL(b.PkgsID,0) when 0 then '1' else '2' end PkgsIsRegular ");
            //sql.Append("   from t_SEOutStockDetail a");
            //sql.Append(" left join v_bdc b on a.PkgsID = b.PkgsID)c ");
            //sql.Append(" where  c.FBillNo ='" + FBillNO + "' and ");
            //sql.Append("c.FSku ='" + FSku + "' and  c.FQty ='" + FQty + "' and  c.StickLen ='" + StickLen + "' and c.PkgsIsRegular ='1'");

            sql.Append("  select count(0) num  from ( ");
            sql.Append("  select FBillNo,FSku,StickLen,FQty, ");
            sql.Append("   case  SUBSTRING(a.HelperCode,1,1)  when 'A' then '1' WHEN 'B' THEN '2' end PkgsIsRegular");
            sql.Append("    from t_SEOutStockDetail a");
            sql.Append("  )c ");
            sql.Append("  where  c.FBillNo ='" + FBillNO + "' and ");
            sql.Append(" c.FSku ='" + FSku + "'  and  c.FQty ='" + FQty + "' and  c.StickLen ='" + StickLen + "' and c.PkgsIsRegular ='1'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.QueryTable(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    i = Convert.ToInt32(dr["num"].ToString().Trim());
                }
            }
            return i;
        }

        /// <summary>
        /// 判断sku、根长、根是否符合（不定尺）1定尺，2不定尺
        /// </summary>
        /// <param name="PkgsBarCode"></param>
        /// <param name="FBillNO"></param>
        /// <returns></returns>
        public int JudgeSkuUnRegular(string PkgsBarCode,string FSku,string FQty,string FBillNO)
        {
            int i = 0;
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable(); 

         
            //sql.Append(" select count(0) num  from ( ");
            //sql.Append(" select FBillNo,FSku,StickLen,FQty, ");
            //sql.Append("  case ISNULL(b.PkgsID,0) when 0 then '1' else '2' end PkgsIsRegular ");
            //sql.Append("   from t_SEOutStockDetail a");
            //sql.Append(" left join v_bdc b on a.PkgsID = b.PkgsID)c ");
            //sql.Append(" where  c.FBillNo ='" + FBillNO + "' and ");
            //sql.Append("c.FSku ='" + FSku + "' and  c.FQty ='" + FQty + "' and  and c.PkgsIsRegular ='2'");

            sql.Append("  select count(0) num  from ( ");
            sql.Append("  select FBillNo,FSku,StickLen,FQty, ");
            sql.Append("   case  SUBSTRING(a.HelperCode,1,1)  when 'A' then '1' WHEN 'B' THEN '2' end PkgsIsRegular");
            sql.Append("    from t_SEOutStockDetail a");
            sql.Append("  )c ");
            sql.Append("  where  c.FBillNo ='" + FBillNO + "' and ");
            sql.Append(" c.FSku ='" + FSku + "'  and  c.FQty ='" + FQty + "'    and c.PkgsIsRegular ='2'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.QueryTable(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    i = Convert.ToInt32(dr["num"].ToString().Trim());
                }
            }
            return i;
        }

        /// <summary>
        /// 判断条码是否属于该出库单j = 0 条码不在销售出库单中；j=1条码在销售出库单中
        /// </summary>
        /// <param name="PkgsBarCode"></param>
        /// <returns></returns>
        public int JudgeSEOutStockDetail(string PkgsBarCode, string FBillNO)
        {
            int j = 0;
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();
            sql.Append("select COUNT(0) num from t_SEOutStockDetail where FBillNo = '" + FBillNO + "' and PkgsBarCode ='" + PkgsBarCode + "' ");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

                dt = DbHelperSQL.QueryTable(sql.ToString());
                if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        j = Convert.ToInt32(dr["num"].ToString().Trim());
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return j;
        }

        /// <summary>
        /// 判断库存中的条码是否被锁k = 0 条码已被锁库；k=1条码未被锁库   
        /// </summary>
        /// <param name="PkgsBarCode"></param>
        /// <returns></returns>
        public int JudgeLockDetail(string PkgsBarCode)
        {
            int k = 0;
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();
            sql.Append("select COUNT(0) num from stk_Package where PkgsBarCode ='" + PkgsBarCode + "' and PkgsStatus='0'");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.QueryTable(sql.ToString());
                if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        k = Convert.ToInt32(dr["num"].ToString().Trim());
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return k;
        }

        /// <summary>
        /// 判断扫码的个数
        /// </summary>
        /// <param name="FBillNo"></param>
        /// <param name="ScanQty"></param>
        /// <returns></returns>
       public int JudgeQty(string FSku,string StickLen,string FQty,string FBillNO,string PkgsIsRegular)
        {
            bool result = false;
            DataTable dt = new DataTable();
            int i = 0;
            StringBuilder sql = new StringBuilder();
           int Qty =0;


           if(PkgsIsRegular =="1")
           {
             sql.Append("select ISNULL(leftqty,0) leftqty from ( ");
             sql.Append("	 select sum( case flag when 0 then 1 else 0 end)  leftqty");
             sql.Append("	 from (");
             sql.Append("		select FBillNo,FSku,StickLen,FQty,a.states,a.flag,");
             sql.Append("		 case  SUBSTRING(a.HelperCode,1,1)  when 'A' then '1' WHEN 'B' THEN '2' end PkgsIsRegular");
             sql.Append("		  from t_SEOutStockDetail a");
             sql.Append("	)c");
             sql.Append("	where  c.FBillNo ='"+FBillNO+"' and c.FSku ='"+FSku+"' and StickLen ='"+StickLen+"' ");
             sql.Append("	 and  c.FQty ='"+FQty+"' and c.PkgsIsRegular ='1') d");
           }
           else if(PkgsIsRegular =="2")
           {
            sql.Append("select ISNULL(leftqty,0) leftqty from ( ");
             sql.Append("	 select sum( case flag when 0 then 1 else 0 end)  leftqty");
             sql.Append("	 from (");
             sql.Append("		select FBillNo,FSku,StickLen,FQty,a.states,a.flag,");
             sql.Append("		 case  SUBSTRING(a.HelperCode,1,1)  when 'A' then '1' WHEN 'B' THEN '2' end PkgsIsRegular");
             sql.Append("		  from t_SEOutStockDetail a");
             sql.Append("	)c");
             sql.Append("	where  c.FBillNo ='"+FBillNO+"' and c.FSku ='"+FSku+"'  ");
             sql.Append("	 and  c.FQty ='"+FQty+"' and c.PkgsIsRegular ='2') d");
           }

             
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.QueryTable(sql.ToString());

                if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        i = Convert.ToInt32(dr["leftqty"].ToString()); 
                       
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return i;
        }

        /// <summary>
        /// 出库，更新条码
        /// </summary>
        /// <param name="PkgsBarCode"></param>
        /// <returns></returns>
        public int UpdatePkgsBarCode(string PkgsBarCode, string FBillNo, string ID, string  Ntime,string plname)
        {
            int l = 0;
            int cknnum = 0;
            string sql2 = "", sql1="";
            StringBuilder sql = new StringBuilder();
            
            //查询预出库的要出库多少，不包含替换，未找到，退件，
            sql1 = "select  count(PosiID) sPosiID,PosiID,PkgsBarCode  from t_SEOutStockDetail where FBillNo='" + FBillNo + "' and Fnumber='"+plname+"' and Sxnum is not null  AND states ='1' AND flag = 0 group by PosiID,PkgsBarCode order by PosiID";
            DataTable deco = DbHelperSQL.QueryTable(sql1);
            if (deco.Rows.Count > 0)
            {
                //查询库存某库位是否是一整拖出库
                sql2 = "select count(PosiID) as PosiID from stk_Package where PosiID in (select PosiID from t_SEOutStockDetail where FBillNo='" + FBillNo + "' and Fnumber='" + plname + "') and PkgsStatus not in (55,99,2,3,4,5,6)";
                DataTable der = DbHelperSQL.QueryTable(sql2);
                if (der.Rows.Count > 0)
                {
                    cknnum = int.Parse(der.Rows[0]["PosiID"].ToString());//库存数量
                }
                //查询预出库是否与库存的仓位拖相等
                if (deco.Rows.Count.Equals(cknnum))
                {
                    for (int i = 0; i < deco.Rows.Count; i++)
                    {
                        sql.Append("update t_SEOutStockDetail set flag = 1,Operator ='" + ID + "',OperatorTime ='" + Ntime + "' where  FBillNo = '" + FBillNo + "' and   PkgsBarCode ='" + deco.Rows[i]["PkgsBarCode"].ToString() + "' and flag = '0'");

                    }
                }
                else
                {
                    sql.Append("update t_SEOutStockDetail set flag = 1,Operator ='" + ID + "',OperatorTime ='" + Ntime + "' where  FBillNo = '" + FBillNo + "' and   PkgsBarCode ='" + PkgsBarCode + "' and flag = '0'");
                }
            }
            else
            {
                sql.Append("update t_SEOutStockDetail set flag = 1,Operator ='" + ID + "',OperatorTime ='" + Ntime + "' where  FBillNo = '" + FBillNo + "' and   PkgsBarCode ='" + PkgsBarCode + "' and flag = '0'");
            }
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                l = DbHelperSQL.ExecuteSql(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return l;
        }


        /// <summary>
        /// 若条码不在本次出库单，则可以插入该条码至出库明细中
        /// </summary>
        /// <param name="PkgsBarCode"></param>
        /// <param name="FBillNo"></param>
        /// <param name="Fry"></param>
        /// <returns></returns>
        public int InsertSEOutStock(string PkgsBarCode, string oldPkgsBarCode,string DFInterID,string DFEntryID,string FDetailID, string FBillNo,string FEntryID,string FSourceBillNo, string Fry,string HelperCode, string t_PkgsIsRegular,string ID)
        {
            int i = 0;
            List<string> sql = new List<string>();
            string Ntime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

            string time = GetTime(oldPkgsBarCode);
            string note = oldPkgsBarCode + "替换为" + PkgsBarCode;
            string j = "",strsql="",sqlw="";
            if (Fry == null || Fry == "")
            {
                Fry = "0";            
            }
            j = ISRepeat(PkgsBarCode);
            string PkCode = "";
            PkCode = GetPkCode(oldPkgsBarCode);


            if (j == "0")
            {

                sql.Add(@" insert into t_SEOutStockDetail( DFInterID,DFEntryID,FDetailID,FBillNo,FEntryID,FSourceBillNo,Fdate,Fry,PkgsID,PkgsBarCode,HelperCode,
              FSku,StickLen,Stick,FQty,StockID,PosiID,Floor,FNumber,PkgsIsRegular,states,flag,note,Operator,OperatorTime)
             select '" + DFInterID + @"' FInterID,'" + DFEntryID + @"' DFEntryID,'" + FDetailID + @"' FDetailID
            ,'" + FBillNo + @"' FBillNo,'" + FEntryID + @"' FEntryID,'" + FSourceBillNo + @"' FSourceBillNo
            ,'" + time + @"' Fdate,'" + Fry + @"' Fry,
            a.PkgsID,a.PkgsBarCode,a.HelperCode,a.ItemSku,a.StickLen,a.Stick,a.FQty
            ,a.StockID,a.PosiID,a.Floor,b.FNumber
			 ,'" + t_PkgsIsRegular + @"' PkgsIsRegular,
			'2' states,'1' flag,'" + note + @"' note,'"+ID+@"','"+Ntime+@"'
              from stk_Package  a
			   left join v_bdc c on a.PkgsID = c.PkgsID
              left join t_StockPlace b on a.PosiID = b.FSPID
             where a.PkgsBarCode = '" + PkgsBarCode + @"'");

                //sql.Add(" update stk_Package set PkCode = '" + PkCode + "' where PkgsBarCode='" + PkgsBarCode + "'");
                sql.Add(" update stk_Package set PkgsStatus=0,HoldBillNo=null,Holder=null,HoldTime=null,PkCode=null where PkgsBarCode='" + oldPkgsBarCode + "'");
                sql.Add("update  t_SEOutStockDetail set flag =2 where PkgsBarCode ='" + oldPkgsBarCode + "' and flag =0");
                sql.Add("update stk_Package set  PkCode = '" + PkCode + "', PkgsStatus=88,HoldBillNo='" + FSourceBillNo + "',Holder=" + Fry + ",HoldTime='" + Ntime + "' where PkgsBarCode='" + PkgsBarCode + "'");
                
                //更新销售订单件明细信息不定尺
                if (HelperCode.Substring(0, 1) == "B")
                {
                    strsql = "select * from stk_PackageDTL where PkgsID in (select PkgsID from stk_Package where PkgsBarCode='" + PkgsBarCode + "') order by Pid";
                    DataTable de=DbHelperSQL.QueryTable(strsql);
                    if (de.Rows.Count > 0)
                    {
                        string str = "";
                        for (int t = 0; t < de.Rows.Count;t++ )
                        {
                            str += "," + de.Rows[t]["PkgTubeLenAct"].ToString().Trim();
                        }
                        sqlw = "select StickLen,Stick,FQty from stk_Package where PkgsBarCode='" + PkgsBarCode + "'";
                        DataTable der = DbHelperSQL.QueryTable(sqlw);
                        if (der.Rows.Count > 0)
                        {
                            sql.Add("update t_SEOrderDetail set XRootLenth='" + der.Rows[0]["StickLen"].ToString() + "',XRootPerPiece='" + der.Rows[0]["Stick"].ToString() + "',XTotalPieceLenth='" + der.Rows[0]["FQty"].ToString() + "',XPieceNum='1',XTotal='" + der.Rows[0]["FQty"].ToString() + "',XRootZLenth='" + str.Substring(1) + "' where PkCode = '" + PkCode + "'");
                        }
                    }
                }

            }
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                i = DbHelperSQL.ExecuteSqlTran(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return i;
        }
        //获取锁库标识
        public string GetPkCode(string PkgsBarCode)
        {
            string PkCode="";
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();
            sql.Append("select PkCode from stk_Package where PkgsBarCode ='" + PkgsBarCode + "'");
            try
            {
                dt = DbHelperSQL.QueryTable(sql.ToString());
                if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        PkCode = dr["PkCode"].ToString();
                    }
                }
                
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return PkCode;
        }

        public string GetTime(string PkgsBarCode)
        {
            string time = "";
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();
            sql.Append(" select  CONVERT(varchar(100), Fdate, 23) Fdate from t_SEOutStockDetail where PkgsBarCode = '" + PkgsBarCode + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.QueryTable(sql.ToString());
                if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        time = dr["Fdate"].ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return time;
        }

        public string ISRepeat(string PkgsBarCode)
        {
            string i = "";
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT count(0) num FROM t_SEOutStockDetail where PkgsBarCode ='" + PkgsBarCode + "' and flag = 1");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

                dt = DbHelperSQL.QueryTable(sql.ToString());
                if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        i = dr["num"].ToString();
                    }
                }


            }
            catch (Exception ex)
            {
                throw ex;
            }
            return i;
        }
    }
}
